﻿using Freshbox2._0.BLL;
using Freshbox2._0.MODEL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Web;
using System.Web.Mvc;
using PagedList;
using System.Text;


namespace Freshbox2._0.UI.Controllers
{
    public class LossController : BaseController<stockloss>
    {
        //
        // GET: /Loss/

        public override BLL.BaseBll<stockloss> bll
        {
            get
            {
                return new StockLossBll();
            }      
        }

        WaresDetailsBll waresdetailBll = new WaresDetailsBll();

        LogBll logBll = new LogBll();

        WaresBll waresBll = new WaresBll();

        // x.lossID, x.lossNumber, x.waresCode, x.waresName, x.waresPrice, x.operateTime,x.storeid

        public class LossModel
        {
            public int? lossNumber { get; set; }
            public string waresCode { get; set; }
            public string waresName { get; set; }
            public decimal? waresPrice { get; set; }
            public decimal? waresAllPrice { get; set; }
        }

        public class LossDetail
        {           
            public int? lossNumber;
            public string lossType;            
        }

        /// <summary>
        /// 动态加载数据  
        /// </summary>

        public ActionResult GetLoss(string date1, string date2, string storeid, int page, int rows,stockloss model)
        {                  
            List<LossModel> LossList = new List<LossModel>();
            //1.查询支付成功的订单  这里多种状态                       
            Expression<Func<stockloss, bool>> condition = null;
            //默认情况   默认查询当天  的所有记录  
            if (string.IsNullOrEmpty(date1) && string.IsNullOrEmpty(date2) && string.IsNullOrEmpty(storeid))
            {
                DateTime todayNow = DateTime.Now.Date;
                DateTime midnight = DateTime.Now.AddDays(+1);
                midnight = midnight.Date;
                condition = x => x.operateTime < midnight && x.operateTime >= todayNow;
            }
            //只按照店铺查询 默认是 查询 今天的 店铺记录  
            if (string.IsNullOrEmpty(date1) && string.IsNullOrEmpty(date2) && storeid != "" && storeid != null)
            {
                int shopid = Convert.ToInt32(storeid);
                DateTime todayNow = DateTime.Now.Date;
                DateTime midnight = DateTime.Now.AddDays(+1);
                midnight = midnight.Date;
                condition = x => x.operateTime <= midnight && x.operateTime >= todayNow && x.storeid == shopid;
            }
            //按照时间和店铺查询   
            if (date1 != null && date1 != "" && date2 != null && date2 != "" && storeid != "")
            {
                int shopid = Convert.ToInt32(storeid);
                DateTime datetime1 = DateTime.Parse(date1);
                DateTime datetime2 = DateTime.Parse(date2).AddDays(1);              
                condition = x => x.operateTime >= datetime1 && x.operateTime <= datetime2 && x.storeid == shopid;   
            }
            //只按照时间查询  所有店铺的
            if (date1 != null && date1 != "" && date2 != null && date2 != "" && string.IsNullOrEmpty(storeid))
            {
                DateTime datetime1 = DateTime.Parse(date1);
                DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
                condition = x => x.operateTime >= datetime1 && x.operateTime <= datetime2;
            }          
            //第二种情况  页数显示准确    商品名称为空
            if (string.IsNullOrEmpty(model.waresName))
            {
                var testListJie = bll.Search(condition).GroupBy(x => x.waresCode).Where(x=>x.FirstOrDefault().num1==2).Select(x => new { x.FirstOrDefault().waresCode, x.FirstOrDefault().waresName, x.FirstOrDefault().waresPrice, lossNumber = -(x.Sum(y => y.lossNumber)), });
                var testListPan = bll.Search(condition).GroupBy(x => x.waresCode).Where(x=>x.FirstOrDefault().num1==1).Select(x => new { x.FirstOrDefault().waresCode, x.FirstOrDefault().waresName, x.FirstOrDefault().waresPrice, lossNumber = x.Sum(y => y.lossNumber), });
                LossModel modelLoss = new LossModel();
                foreach (var item in testListJie)
                {
                 
                    modelLoss.lossNumber = item.lossNumber;
                    modelLoss.waresCode = item.waresCode;
                    modelLoss.waresName = item.waresName;
                    modelLoss.waresPrice = item.waresPrice;
                    modelLoss.waresAllPrice = item.lossNumber * item.waresPrice;
                    LossList.Add(modelLoss);
                }
                foreach (var item in testListPan)
                {

                    modelLoss.lossNumber = item.lossNumber;
                    modelLoss.waresCode = item.waresCode;
                    modelLoss.waresName = item.waresName;
                    modelLoss.waresPrice = item.waresPrice;
                    modelLoss.waresAllPrice = item.lossNumber * item.waresPrice;
                    LossList.Add(modelLoss);
                }
                var LossList2= LossList.GroupBy(x => x.waresCode).Select(x => new { x.FirstOrDefault().waresCode, x.FirstOrDefault().waresName, x.FirstOrDefault().waresPrice, lossNumber = x.Sum(y => y.lossNumber), }).ToList();
                //foreach (var item in testList)
                //{
                //    LossModel modelLoss = new LossModel();
                //    modelLoss.lossNumber = item.lossNumber;
                //    modelLoss.waresCode = item.waresCode;
                //    modelLoss.waresName = item.waresName;
                //    modelLoss.waresPrice = item.waresPrice;
                //    modelLoss.waresAllPrice = item.lossNumber * item.waresPrice;
                //    LossList.Add(modelLoss);
                //}
                var listPage = LossList2.ToPagedList(page, rows).ToList();//ToPagedList  分页  
                return Json(new { rows = listPage, total = LossList2.Count() });   
            }
            //根据商品名称查询
            else
            {
                var listOrder = bll.Search(condition).Where(x => x.waresName.Contains(model.waresName)).ToList();
                var list = listOrder.GroupBy(x => x.waresCode).Select(x => new { lossNumber = x.Sum(y => y.lossNumber), x.FirstOrDefault().waresCode, x.FirstOrDefault().waresName, x.FirstOrDefault().waresPrice }).ToList();
                foreach (var item in list)
                {
                    LossModel modelLoss = new LossModel();
                    modelLoss.lossNumber = item.lossNumber;
                    modelLoss.waresCode = item.waresCode;
                    modelLoss.waresName = item.waresName;
                    modelLoss.waresPrice = item.waresPrice;
                    modelLoss.waresAllPrice = modelLoss.lossNumber * modelLoss.waresPrice;
                    LossList.Add(modelLoss);
                }
                var listPage = LossList.ToPagedList(page, rows).ToList();//ToPagedList  分页  
                return Json(new { rows = listPage, total = LossList.Count() });   
            }
            
        }

        /// <summary>
        /// 点击加号显示  结算台超出的  
        /// </summary>

        public ActionResult GetDetailByID(string waresCode, string date1, string date2,string storeid)
        {
            var listLossDetail = new List<LossDetail>();
            Expression<Func<stockloss, bool>> condition = null;
            //默认情况   默认查询当天  的所有记录  
            if (string.IsNullOrEmpty(date1) && string.IsNullOrEmpty(date2) && string.IsNullOrEmpty(storeid))
            {
                DateTime todayNow = DateTime.Now.Date;
                DateTime midnight = DateTime.Now.AddDays(+1);
                midnight = midnight.Date;
                condition = x => x.operateTime < midnight && x.operateTime >= todayNow && x.waresCode == waresCode;
            }
            //只按照店铺查询 默认是 查询 今天的 店铺记录  
            if (string.IsNullOrEmpty(date1) && string.IsNullOrEmpty(date2) && storeid != "" && storeid != null)
            {
                int shopid = Convert.ToInt32(storeid);
                DateTime todayNow = DateTime.Now.Date;
                DateTime midnight = DateTime.Now.AddDays(+1);
                midnight = midnight.Date;
                condition = x => x.operateTime <= midnight && x.operateTime >= todayNow && x.storeid == shopid && x.waresCode == waresCode;
            }
            //按照时间和店铺查询   
            if (date1 != null && date1 != "" && date2 != null && date2 != "" && storeid != "")
            {
                int shopid = Convert.ToInt32(storeid);
                DateTime datetime1 = DateTime.Parse(date1);
                DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
                condition = x => x.operateTime >= datetime1 && x.operateTime <= datetime2 && x.storeid == shopid && x.waresCode == waresCode;
            }
            //只按照时间查询  所有店铺的
            if (date1 != null && date1 != "" && date2 != null && date2 != "" && string.IsNullOrEmpty(storeid))
            {
                DateTime datetime1 = DateTime.Parse(date1);
                DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
                condition = x => x.operateTime >= datetime1 && x.operateTime <= datetime2 && x.waresCode == waresCode;
            }     
            var list = bll.Search().Where(condition).GroupBy(x=>x.num1).Select(x => new {num1= x.FirstOrDefault().num1==1?"盘库差量":"结算台差量",lossNumber=x.Sum(y=>y.lossNumber)}).ToList();
            
            foreach (var oneDetailCode in list)
            {
               
                if (list.Count != 0)
                {
                    LossDetail detail = new LossDetail();       
                    if (oneDetailCode.num1.Equals("结算台差量"))
                    {
                        detail.lossNumber = -(oneDetailCode.lossNumber);
                        detail.lossType = oneDetailCode.num1;
                        listLossDetail.Add(detail);
                    }
                    else
                    {
                        detail.lossNumber = oneDetailCode.lossNumber;
                        detail.lossType = oneDetailCode.num1;
                        listLossDetail.Add(detail);
                    }

                } 
            }
            return Json(listLossDetail);
        }
        

        public int  lossNum =0;//丢失数量

        public int upNum = 0;//上架的数量数量

        public decimal allMoney = 0.00m;//总金额

        public decimal lossMoney = 0.00m;//亏损金额

        /// <summary>
        /// 计算总的亏损情况   货损率
        /// </summary>
        /// <param name="date1"></param>
        /// <param name="date2"></param>
        /// <param name="storeid"></param>
        /// <param name="model"></param>
        /// <returns></returns>
                                                                                                   
        public ActionResult GetAll(string date1, string date2, string storeid, stockloss model)
        {          
            List<LossModel> LossList = new List<LossModel>();

            //1. 查询支付成功的订单     这里多种状态                        
            Expression<Func<stockloss, bool>> condition = null;

            Expression<Func<log, bool>> conditionLog = null;  

            //默认情况   默认查询今天的所有记录  
            if (string.IsNullOrEmpty(date1) && string.IsNullOrEmpty(date2) && string.IsNullOrEmpty(storeid))
            {
                DateTime todayNow = DateTime.Now.Date;
                DateTime midnight = DateTime.Now.AddDays(+1);
                midnight = midnight.Date;
                condition = x => x.operateTime <= midnight && x.operateTime >= todayNow;
                conditionLog = x => x.logTime <= midnight && x.logTime >= todayNow;
            }
            //只按照店铺查询 默认是查询一周的店铺记录  
            if (string.IsNullOrEmpty(date1) && string.IsNullOrEmpty(date2) && storeid != "" && storeid != null)
            {
                int shopid = Convert.ToInt32(storeid);
                DateTime todayNow = DateTime.Now;
                DateTime midnight = DateTime.Now.AddDays(+1);
                midnight = midnight.Date;
                condition = x => x.operateTime <= midnight && x.operateTime >= todayNow && x.storeid == shopid;
                conditionLog = x => x.logTime <= midnight && x.logTime >= todayNow && x.storeCode == shopid;
            }
            //按照时间和店铺查询   
            if (date1 != null && date1 != "" && date2 != null && date2 != "" && storeid != "")
            {
                int shopid = Convert.ToInt32(storeid);
                DateTime datetime1 = DateTime.Parse(date1);
                DateTime datetime2 = DateTime.Parse(date2).AddDays(1);

                condition = x => x.operateTime >= datetime1 && x.operateTime <= datetime2 && x.storeid == shopid;
                conditionLog = x => x.logTime <= datetime2 && x.logTime >= datetime1 && x.storeCode == shopid;
            }
            //只按照时间查询  所有店铺的
            if (date1 != null && date1 != "" && date2 != null && date2 != "" &&  string.IsNullOrEmpty(storeid))
            {
                DateTime datetime1 = DateTime.Parse(date1);
                DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
                condition = x => x.operateTime >= datetime1 && x.operateTime <= datetime2;
                conditionLog = x => x.logTime <= datetime2 && x.logTime >= datetime1;
            }
            //  是否有 商品名称
            if (string.IsNullOrEmpty(model.waresName))
            {
                //基数
                var listlog =logBll.Search(conditionLog).ToList();
                var listlogOrder = listlog.GroupBy(x => x.waresCode).Select(x => new { num = x.Sum(y =>y.beforeUpdate- y.afterUpdate),x.FirstOrDefault().waresCode}).ToList();
                foreach (var itemLog in listlogOrder)
	            {
                    decimal? priceWares = waresBll.Search(x => x.waresCode == itemLog.waresCode).Select(x => new { x.waresPrice, x.waresBid }).ToList().FirstOrDefault().waresBid;
                    decimal price = Convert.ToDecimal(priceWares);
                    decimal a=  price* Convert.ToInt32(itemLog.num);
                    allMoney = allMoney + a;
	            }

                //亏损量
                var listOrder = bll.Search(condition).ToList();
                var list = listOrder.GroupBy(x => x.waresCode).Select(x => new { lossNumber = x.Sum(y => y.lossNumber), x.FirstOrDefault().waresCode, x.FirstOrDefault().waresName, x.FirstOrDefault().waresPrice ,x.FirstOrDefault().waresSalePrice}).ToList();
                foreach (var item in list)
                {
                    LossModel modelLoss = new LossModel();
                    modelLoss.lossNumber = item.lossNumber;
                    modelLoss.waresCode = item.waresCode;
                    modelLoss.waresName = item.waresName;
                    modelLoss.waresPrice = item.waresPrice;                   
                    modelLoss.waresAllPrice = modelLoss.lossNumber * modelLoss.waresPrice;
                    LossList.Add(modelLoss);
                    lossNum = lossNum + Convert.ToInt32(modelLoss.lossNumber);
                    lossMoney = lossMoney + Convert.ToDecimal(modelLoss.waresAllPrice);
                }
            }
             //根据商品名称查询
            else 
            {
                var listOrder = bll.Search(condition).Where(x=>x.waresName.Contains(model.waresName)).ToList();
                var list = listOrder.GroupBy(x => x.waresCode).Select(x => new { lossNumber = x.Sum(y => y.lossNumber), x.FirstOrDefault().waresCode, x.FirstOrDefault().waresName, x.FirstOrDefault().waresPrice }).ToList();
                foreach (var item in list)
                {
                    LossModel modelLoss = new LossModel();
                    modelLoss.lossNumber = item.lossNumber;
                    modelLoss.waresCode = item.waresCode;
                    modelLoss.waresName = item.waresName;
                    modelLoss.waresPrice = item.waresPrice;
                    modelLoss.waresAllPrice = modelLoss.lossNumber * modelLoss.waresPrice;
                    LossList.Add(modelLoss);

                    lossNum = lossNum + Convert.ToInt32(modelLoss.lossNumber);
                    lossMoney = lossMoney + Convert.ToDecimal(modelLoss.waresAllPrice);
                }
            }
            decimal zheng=Math.Abs(lossMoney);
            string rate = "";
            if (allMoney != 0)
            {
                rate = (zheng / allMoney).ToString("P");
            }
            else
            {
                rate = "";
            }
            // rate = (zheng / allMoney).ToString("P")   #.##
            return Json(new { rows = LossList, lossNum = lossNum, lossMoney = lossMoney, rate = rate }, JsonRequestBehavior.AllowGet);
        }

        /// 计算总的亏损情况   货损率  BySql
        
        public ActionResult GetAllBySql(string date1, string date2, string storeid, stockloss model)
        {
            StringBuilder sql1 = new StringBuilder();
            StringBuilder sql2 = new StringBuilder();
            List<LossModel> LossList = new List<LossModel>();
            sql1.Append("SELECT SUM(lossNumber) as lossNumber ,waresCode ,waresPrice,waresName,SUM(lossNumber)*waresPrice as waresAllPrice  from  stockloss    where ");
            sql2.Append("SELECT SUM(-lossNumber) as lossNumber ,waresCode ,waresPrice,waresName,-SUM(lossNumber)*waresPrice as waresAllPrice  from  stockloss   where ");

            Expression<Func<log, bool>> conditionLog = null;

            //默认情况   默认查询今天的所有记录  
            if (string.IsNullOrEmpty(date1) && string.IsNullOrEmpty(date2) && string.IsNullOrEmpty(storeid))
            {
                DateTime todayNow = DateTime.Now.Date;
                DateTime midnight = DateTime.Now.AddDays(+1);
                midnight = midnight.Date;
                sql1.Append("   num1=1  and operateTime between '" + todayNow + "'  and  '" + midnight + "'  ");
                sql2.Append("   num1=2  and operateTime between '" + todayNow + "'  and  '" + midnight + "'  ");
                conditionLog = x => x.logTime <= midnight && x.logTime >= todayNow;
            }
            //只按照店铺查询 默认是查询一周的店铺记录  
            if (string.IsNullOrEmpty(date1) && string.IsNullOrEmpty(date2) && storeid != "" && storeid != null)
            {
                int shopid = Convert.ToInt32(storeid);
                DateTime todayNow = DateTime.Now.Date;
                DateTime midnight = DateTime.Now.AddDays(+1);
                midnight = midnight.Date;
                sql1.Append("   num1=1  and operateTime between '" + todayNow + "'  and  '" + midnight + "'  and storeid=" + shopid + " ");
                sql2.Append("   num1=2  and operateTime between '" + todayNow + "'  and  '" + midnight + "'  and storeid=" + shopid + "   ");
                conditionLog = x => x.logTime <= midnight && x.logTime >= todayNow && x.storeCode == shopid;
            }
            //按照时间和店铺查询   
            if (date1 != null && date1 != "" && date2 != null && date2 != "" && storeid != "")
            {
                int shopid = Convert.ToInt32(storeid);
                DateTime datetime1 = DateTime.Parse(date1);
                DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
                sql1.Append(" num1=1  and operateTime between '" + datetime1 + "'  and  '" + datetime2 + "'  and storeid=" + shopid + "  ");
                sql2.Append(" num1=2  and operateTime between '" + datetime1 + "'  and  '" + datetime2 + "'  and storeid=" + shopid + " ");
                conditionLog = x => x.logTime <= datetime2 && x.logTime >= datetime1 && x.storeCode == shopid;
            }
            //只按照时间查询  所有店铺的
            if (date1 != null && date1 != "" && date2 != null && date2 != "" && string.IsNullOrEmpty(storeid))
            {
                DateTime datetime1 = DateTime.Parse(date1);
                DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
                sql1.Append(" num1=1  and operateTime between '" + datetime1 + "'  and  '" + datetime2 + "'  ");
                sql2.Append(" num1=2  and operateTime between '" + datetime1 + "'  and  '" + datetime2 + "' ");
                conditionLog = x => x.logTime <= datetime2 && x.logTime >= datetime1;
            }
            //  是否有 商品名称
            if (string.IsNullOrEmpty(model.waresName))
            {
                //基数
                var listlog = logBll.Search(conditionLog).ToList();
                var listlogOrder = listlog.GroupBy(x => x.waresCode).Select(x => new { num = x.Sum(y => y.beforeUpdate - y.afterUpdate), x.FirstOrDefault().waresCode }).ToList();
                foreach (var itemLog in listlogOrder)
                {
                    decimal? priceWares = waresBll.Search(x => x.waresCode == itemLog.waresCode).Select(x => new { x.waresPrice, x.waresBid }).ToList().FirstOrDefault().waresBid;
                    decimal price = Convert.ToDecimal(priceWares);
                    decimal a = price * Convert.ToInt32(itemLog.num);
                    allMoney = allMoney + a;
                }

                //亏损量
                sql1.Append(" GROUP BY waresCode");
                sql2.Append(" GROUP BY waresCode");
                var list1 = bll.SearchBySQL<LossModel>(sql1.ToString()).ToList();
                var list2 = bll.SearchBySQL<LossModel>(sql2.ToString()).ToList();
                var LossList1 = list1.Union(list2).ToList();
                lossNum =Convert.ToInt32( LossList1.Sum(x => x.lossNumber));
                lossMoney =Convert.ToDecimal( LossList1.Sum(x => x.waresAllPrice));
            }
            //根据商品名称查询 
            else
            {
                //基数
                var listlog = logBll.Search(conditionLog).Where(x=>x.wares.waresName.Contains(model.waresName)).ToList();
                var listlogOrder = listlog.GroupBy(x => x.waresCode).Select(x => new { num = x.Sum(y => y.beforeUpdate - y.afterUpdate), x.FirstOrDefault().waresCode }).ToList();
                foreach (var itemLog in listlogOrder)
                {
                    decimal? priceWares = waresBll.Search(x => x.waresCode == itemLog.waresCode).Select(x => new { x.waresPrice, x.waresBid }).ToList().FirstOrDefault().waresBid;
                    decimal price = Convert.ToDecimal(priceWares);
                    decimal a = price * Convert.ToInt32(itemLog.num);
                    allMoney = allMoney + a;
                }

                sql1.Append(" AND  waresName LIKE '%" + model.waresName + "%'  GROUP BY waresCode");
                sql2.Append(" AND  waresName LIKE '%" + model.waresName + "%'  GROUP BY waresCode");
                var list1 = bll.SearchBySQL<LossModel>(sql1.ToString());
                var list2 = bll.SearchBySQL<LossModel>(sql2.ToString());
                var LossList1 = list1.Union(list2).ToList();
                lossNum = Convert.ToInt32(LossList1.Sum(x => x.lossNumber));
                lossMoney = Convert.ToDecimal(LossList1.Sum(x => x.waresAllPrice));

            }
            decimal zheng = Math.Abs(lossMoney);
            string rate = "";
            if (allMoney != 0)
            {
                rate = (zheng / allMoney).ToString("P");
            }
            else
            {
                rate = "";
            }
            // rate = (zheng / allMoney).ToString("P")   #.##
            return Json(new { rows = LossList, lossNum = lossNum, lossMoney = lossMoney, rate = rate }, JsonRequestBehavior.AllowGet);
        }

        StoreBll storeBll=new StoreBll();

        public ActionResult GetStore()
        {
            return Json(storeBll.Search().Select(x => new { StoreID = x.storeid, StoreName = x.storeName }).ToList());
        }

        public ActionResult GetLossBySql(string date1, string date2, string storeid, int page, int rows, stockloss model)
        {
            StringBuilder sql1 = new StringBuilder();
            StringBuilder sql2 = new StringBuilder();
            List<LossModel> LossList = new List<LossModel>();
            sql1.Append("SELECT SUM(lossNumber) as lossNumber ,waresCode ,waresPrice,waresName,SUM(lossNumber)*waresPrice as waresAllPrice  from  stockloss    where ");
            sql2.Append("SELECT SUM(-lossNumber) as lossNumber ,waresCode ,waresPrice,waresName,-SUM(lossNumber)*waresPrice as waresAllPrice  from  stockloss   where ");
            //1.查询支付成功的订单  这里多种状态                       
            //Expression<Func<stockloss, bool>> condition = null;
            //默认情况   默认查询当天  的所有记录  
            if (string.IsNullOrEmpty(date1) && string.IsNullOrEmpty(date2) && string.IsNullOrEmpty(storeid))
            {
                DateTime todayNow = DateTime.Now.Date;
                DateTime midnight = DateTime.Now.AddDays(+1);
                midnight = midnight.Date;
                sql1.Append("   num1=1  and operateTime between '" + todayNow + "'  and  '" + midnight + "'  ");
                sql2.Append("   num1=2  and operateTime between '" + todayNow + "'  and  '" + midnight + "'  ");
            }
            //只按照店铺查询 默认是 查询 今天的 店铺记录  
            if (string.IsNullOrEmpty(date1) && string.IsNullOrEmpty(date2) && storeid != "" && storeid != null)
            {
                int shopid = Convert.ToInt32(storeid);
                DateTime todayNow = DateTime.Now.Date;
                DateTime midnight = DateTime.Now.AddDays(+1);
                midnight = midnight.Date;
                sql1.Append("   num1=1  and operateTime between '" + todayNow + "'  and  '" + midnight + "'  and storeid=" + shopid + " ");
                sql2.Append("   num1=2  and operateTime between '" + todayNow + "'  and  '" + midnight + "'  and storeid=" + shopid + "   ");
               // condition = x => x.operateTime <= midnight && x.operateTime >= todayNow && x.storeid == shopid;
            }
            //按照时间和店铺查询   
            if (date1 != null && date1 != "" && date2 != null && date2 != "" && storeid != "")
            {
                int shopid = Convert.ToInt32(storeid);
                DateTime datetime1 = DateTime.Parse(date1);
                DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
                sql1.Append(" num1=1  and operateTime between '" + datetime1 + "'  and  '" + datetime2 + "'  and storeid=" + shopid + "  ");
                sql2.Append(" num1=2  and operateTime between '" + datetime1 + "'  and  '" + datetime2 + "'  and storeid=" + shopid + " ");
                //condition = x => x.operateTime >= datetime1 && x.operateTime <= datetime2 && x.storeid == shopid;
            }
            //只按照时间查询  所有店铺的
            if (date1 != null && date1 != "" && date2 != null && date2 != "" && string.IsNullOrEmpty(storeid))
            {
                DateTime datetime1 = DateTime.Parse(date1);
                DateTime datetime2 = DateTime.Parse(date2).AddDays(1);
                sql1.Append(" num1=1  and operateTime between '" + datetime1 + "'  and  '" + datetime2 + "'  ");
                sql2.Append(" num1=2  and operateTime between '" + datetime1 + "'  and  '" + datetime2 + "' ");
               // condition = x => x.operateTime >= datetime1 && x.operateTime <= datetime2;
            }
            //第二种情况  页数显示准确    商品名称为空 .Select(x => new { x.waresCode, x.waresName, x.waresPrice, x.lossNumber, x.waresAllPrice })
            if (string.IsNullOrEmpty(model.waresName))
            {
                sql1.Append(" GROUP BY waresCode");
                sql2.Append(" GROUP BY waresCode");
                var list1 = bll.SearchBySQL<LossModel>(sql1.ToString()).ToList();
                var list2 = bll.SearchBySQL<LossModel>(sql2.ToString()).ToList();
                var LossList1 = list1.Union(list2).GroupBy(x => x.waresCode).Select(x => new { x.FirstOrDefault().waresCode, x.FirstOrDefault().waresName, x.FirstOrDefault().waresPrice, lossNumber = x.Sum(y => y.lossNumber), waresAllPrice = x.FirstOrDefault().waresPrice * x.Sum(y => y.lossNumber) }).ToList();
                var listPage = LossList1.ToPagedList(page, rows).ToList();//ToPagedList  分页  
                return Json(new { rows = listPage, total = LossList1.Count() });
            }
            //根据商品名称查询
            else
            {
                sql1.Append(" AND  waresName LIKE '%"+ model.waresName+"%'  GROUP BY waresCode");
                sql2.Append(" AND  waresName LIKE '%" + model.waresName + "%'  GROUP BY waresCode");
                var list1 = bll.SearchBySQL<LossModel>(sql1.ToString());
                var list2 = bll.SearchBySQL<LossModel>(sql2.ToString());
                var LossList1 = list1.Union(list2).GroupBy(x => x.waresCode).Select(x => new { x.FirstOrDefault().waresCode, x.FirstOrDefault().waresName, x.FirstOrDefault().waresPrice, lossNumber = x.Sum(y => y.lossNumber), waresAllPrice = x.FirstOrDefault().waresPrice * x.Sum(y => y.lossNumber) }).ToList();
                var listPage = LossList1.ToPagedList(page, rows).ToList();//ToPagedList  分页  
                return Json(new { rows = listPage, total = LossList1.Count() });            
            }        
        }


    }
}
